﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ZEntity.Data.Helper
{
    /// <summary>
    /// 数据库操作类（单例模式）
    /// </summary>
    public class SQLHelper
    {
        private static SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        private DataSet ds = new DataSet();
        private SqlDataAdapter sda = null;
        private static SQLHelper m_instance = null;
        private static string connStr = string.Empty;

        public SQLHelper() {
            if (ConfigurationManager.ConnectionStrings["sqlServer"] != null) {
                connStr = ConfigurationManager.ConnectionStrings["sqlServer"].ConnectionString;
                conn = new SqlConnection(connStr);
            }
        }

        /// <summary>
        /// 设置连接字符串
        /// </summary>
        public static void SetConn(string connString) {   
            connStr = connString;
            if (conn == null) {
                conn = new SqlConnection(connStr);
            } else {
                conn.ConnectionString = connStr;
            }
        }


        //
        //单例模式取实例
        public static SQLHelper GetInstance() {
            if (m_instance == null) {
                m_instance = new SQLHelper();
            }
            return m_instance;
        }

        //
        //  打开数据库连接
        // 
        private SqlConnection GetConn() {
            if (conn.State == ConnectionState.Closed) {
                conn.Open();
            }
            return conn;
        }


        /// <summary>
        ///  该方法执行传入的SQL“增、删、改”语句或存储过程
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="ct">语句类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType ct) {
            int res;
            try {
                cmd = new SqlCommand(cmdText, GetConn());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            } catch (Exception ex) {

                throw ex;
            } finally {
                if (conn.State == ConnectionState.Open) {
                    conn.Close();
                }
            }
            return res;
        }

        /// <summary>
        /// 该方法执行"带参数"传入的SQL“增、删、改”语句或存储过程
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="paras">参数</param>
        ///  /// <param name="ct">语句类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct) {

            int res;
            using (cmd = new SqlCommand(cmdText, GetConn())) {
                cmd.Parameters.AddRange(paras);
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }

            return res;

        }
        /// <summary>
        /// 该方法执行传入的SQL“查询”语句或存储过程
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="ct">语句类型</param>
        /// <returns>返回数据表--DataTable类型</returns>
        public DataTable ExecuteQuery(string cmdText, CommandType ct) {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                dt.Load(sdr);
            }
            return dt;
        }

        /// <summary>
        ///  该方法执行"带参数"传入的SQL“查询”语句或存储过程
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">语句类型</param>
        /// <returns>返回数据表--DataTable类型</returns>
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct) {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.Parameters.AddRange(paras);
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                dt.Load(sdr);
            }
            return dt;
        }


        /// <summary>
        /// 根据输入的SQL语句检索数据库数据
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="tableName">内存中表名</param>
        /// <returns></returns>
        public DataSet SelectDataBase(string cmdText, string tableName) {
            sda = new SqlDataAdapter(cmdText, conn);
            ds.Clear();
            sda.Fill(ds, tableName);
            return ds;
        }

        /// <summary>
        /// 查询单列值
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, CommandType ct) {
            using (cmd = new SqlCommand(cmdText, GetConn())) {
                cmd.CommandType = ct;
                object result = cmd.ExecuteScalar();
                return result;
            }
        }

        #region 仅有sql语句参数的DB执行和查询
        /// <summary>
        ///  该方法执行传入的SQL“增、删、改”语句, 只是SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText) {
            return ExecuteNonQuery(cmdText, CommandType.Text);
        }
        /// <summary>
        /// 该方法执行传入的SQL“查询”语句，仅SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param> 
        /// <returns>返回数据表--DataTable类型</returns>
        public DataTable ExecuteQuery(string cmdText) {
            return ExecuteQuery(cmdText, CommandType.Text);
        }

        /// <summary>
        /// 该方法执行传入的SQL“查询”语句或存储过程
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="ct">语句类型</param>
        /// <param name="connType">连接方式</param>
        /// <returns>返回数据表--DataTable类型</returns>
        public DataTable ExecuteQuery(string cmdText, CommandType ct, EnumDBConnType connType) {
            if (connType == EnumDBConnType.Single) {
                return ExecuteQuery(cmdText, ct);
            }

            DataTable dt = new DataTable();
            SqlConnection conection = new SqlConnection(connStr);
            conection.Open();
            try {
                cmd = new SqlCommand(cmdText, conection);
                cmd.CommandType = ct;
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                    dt.Load(sdr);
                }
            } catch (Exception ex) {
                throw ex;
            } finally {
                if (conection.State == ConnectionState.Open) {
                    conection.Close();
                }
            }
            return dt;
        }

        #endregion
    }

    public enum EnumDBConnType
    {
        Single = 0,
        AlwaysNew = 1
    }
}